const sqlConnect = require('../utils/sqlserver2142')//通勤车运营数据数据库
const sqlConn2132 = require('../utils/sqlserver2132')//考勤数据库

const dayjs = require('dayjs');

//通勤车-统计运营趟次-逻辑在2142的api中
exports.getCostRecord = async (req, res) => {
   try {
      const result = `select * from CommuteBus_Coast where 运营日期>='${req.body.startDate}' and 运营日期<='${req.body.endDate}'`;
      const costResult = await sqlConnect(result);
      const records = costResult && costResult.recordsets && costResult.recordsets[0] ? costResult.recordsets[0] : [];
      res.json({
         code: 200,
         message: `success`,
         recordCount: records.length,
         data: records
      });
   } catch (error) {
      res.status(500).json({
         code: 500,
         message: '查询失败',
         error: error.message
      });
   }
}

//通勤车-查询破坏规则的列表
exports.getBreakRuleList = async (req, res) => {
   try {
      const moningstarttime = '04:00:00'
      const moningendtime = '09:00:00'
      const nightstarttime = '17:30:00'
      const nightendtime = '23:00:00'
      let result = ''
      if (req.body.startDate == "0625") {
         result = `select UserID,WhichSeat,ScanQrDateTime,UserUnit from CommuteBusYuyue_Report where GoDateTime=FORMAT(DATEADD(DAY,0,GETDATE()),'yyyy-MM-dd  06:25:00')`;
      } else {
         result = `select UserID,WhichSeat,ScanQrDateTime,UserUnit from CommuteBusYuyue_Report where GoDateTime=FORMAT(DATEADD(DAY,-1,GETDATE()),'yyyy-MM-dd  17:50:00')`;
      }
      const breakRuleResult = await sqlConnect(result);
      const breakRules = breakRuleResult && breakRuleResult.recordsets && breakRuleResult.recordsets[0] ? breakRuleResult.recordsets[0] : [];
      // console.log('breakRules', breakRules)
      if(breakRules.length == 0){
         res.json({
            code: 200,
            message: `没有用户数据`,
            recordCount: 0,
            data: []
         });
         return
      }
      let userid = ''
      for (let i = 0; i < breakRules.length; i++) {

         userid = userid + "'" + breakRules[i].UserID + "',";
      }
      userid = userid.substring(0, userid.length - 1);

      let sqlstr = '';

      if (req.body.startDate == "0625") {
         sqlstr = `SELECT u.badgenumber,u.name
                           FROM userinfo u
                           LEFT JOIN checkinout c ON u.userid = c.userid
                           AND c.checktime BETWEEN CAST(CAST(getdate() AS date) AS datetime) + '${moningstarttime}' AND 
                           CAST(CAST(getdate() AS date) AS datetime) + '${moningendtime}'
                           WHERE u.badgenumber IN (${userid})
                           AND c.userid IS NULL`
      } else {
         sqlstr = `SELECT u.badgenumber,u.name
                           FROM userinfo u
                           LEFT JOIN checkinout c ON u.userid = c.userid
                           AND c.checktime BETWEEN CAST(CAST(getdate()-1 AS date) AS datetime) + '${nightstarttime}' AND 
                           CAST(CAST(getdate()-1 AS date) AS datetime) + '${nightendtime}'
                           WHERE u.badgenumber IN (${userid})
                           AND c.userid IS NULL`
      }
      // console.log(sqlstr1)
      const kqResult = await sqlConn2132(sqlstr);
      const kqRecords = kqResult && kqResult.recordsets && kqResult.recordsets[0] ? kqResult.recordsets[0] : [];
      // console.log('kqRecords', kqRecords)
      if(kqRecords.length == 0){
         res.json({
            code: 200,
            message: `没有破坏规则的用户`,
            recordCount: 0,
            data: []
         });
         return
      }
      for (let k = 0; k < kqRecords.length; k++) {
         const x = breakRules.find(item => item.UserID === kqRecords[k].badgenumber)
         const y = kqRecords.find(item => item.badgenumber === kqRecords[k].badgenumber)
         const { name } = y
         // console.log('y', y)
         if (req.body.startDate == "0625") {
            if (x.ScanQrDateTime)
               sqlstr = `insert into CommuteBusYuyue_BreakRule (UserUnit,UserName,UserID,WhichSeat,ScanQrDateTime,CreateDate) values ('${x.UserUnit}','${name}','${x.UserID}','${x.WhichSeat}','${dayjs(x.ScanQrDateTime).format('YYYY-MM-DD HH:mm:ss')}','${dayjs().format('YYYY-MM-DD HH:mm:ss')}')`
            else
               sqlstr = `insert into CommuteBusYuyue_BreakRule (UserUnit,UserName,UserID,WhichSeat,CreateDate) values ('${x.UserUnit}','${name}','${x.UserID}','${x.WhichSeat}','${dayjs().format('YYYY-MM-DD HH:mm:ss')}')`

         }
         else {
            if (x.ScanQrDateTime)
               sqlstr = `insert into CommuteBusYuyue_BreakRule (UserUnit,UserName,UserID,WhichSeat,ScanQrDateTime,CreateDate) values ('${x.UserUnit}','${name}','${x.UserID}','${x.WhichSeat}','${dayjs(x.ScanQrDateTime).add(-8, 'hour').format('YYYY-MM-DD HH:mm:ss')}','${dayjs().format('YYYY-MM-DD HH:mm:ss')}')`
            else
               sqlstr = `insert into CommuteBusYuyue_BreakRule (UserUnit,UserName,UserID,WhichSeat,CreateDate) values ('${x.UserUnit}','${name}','${x.UserID}','${x.WhichSeat}','${dayjs().format('YYYY-MM-DD HH:mm:ss')}')`
         }
         await sqlConnect(sqlstr);
      }
      res.json({
         code: 200,
         message: `success`,
         recordCount: kqRecords.length,
         data: kqRecords
      });
   } catch (error) {
      res.status(500).json({
         code: 500,
         message: '查询失败',
         error: error.message
      });
   }
}

exports.getBRList = async (req, res) => {
   try {
      const result = `select * from CommuteBusYuyue_BreakRule where CreateDate>='${req.body.startDate}' and CreateDate<='${req.body.endDate}' order by CreateDate,UserUnit`;
      const breakRuleResult = await sqlConnect(result);
      const breakRules = breakRuleResult && breakRuleResult.recordsets && breakRuleResult.recordsets[0] ? breakRuleResult.recordsets[0] : [];
      res.json({
         code: 200,
         message: `success`,
         recordCount: breakRules.length,
         data: breakRules
      });
   } catch (error) {
      res.status(500).json({
         code: 500,
         message: '查询失败',
         error: error.message
      });
   }
}
